﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using GMS.Dinasty.BO;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace GMS.Dinasty.DAL
{
    public class DataAccessGoods
    {
        public SqlDataReader Test()
        {
            string cs = "server=pavel;uid=pavel;pwd=123456;database=GMSDatabase";
            SqlConnection conn = new SqlConnection(cs);
            SqlCommand selectContact = new SqlCommand("DSelectNumberBillByUser", conn);
            conn.Open();
            SqlDataReader reader = selectContact.ExecuteReader();
            reader.Read();
            return reader;
        }
        public DataTable PropertiesTable(string StoredProcedure, string ConnectionString, int IdGood)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            SqlCommand select = new SqlCommand();
            select.Connection = connection;
            select.CommandText = StoredProcedure;
            select.CommandType = CommandType.StoredProcedure;
            select.Parameters.Add("@IdGoods", SqlDbType.Int).Value = IdGood;
            DataTable properties = new DataTable();
            DataColumn dc = new DataColumn("IdProperties");
            properties.Columns.Add(dc);
            dc = new DataColumn("CaptionProperties");
            properties.Columns.Add(dc);
            dc = new DataColumn("DescribeProperties");
            properties.Columns.Add(dc);
            dc = new DataColumn("PropertieValue");
            properties.Columns.Add(dc);

            SqlDataReader reader;

            try
            {
                connection.Open();
                reader = select.ExecuteReader();
                while (reader.Read())
                {
                    DataRow dr = properties.NewRow();
                    dr["IdProperties"] = reader.GetInt32(reader.GetOrdinal("IdProperties"));
                    dr["CaptionProperties"] = reader.GetString(reader.GetOrdinal("CaptionProperties"));
                    dr["DescribeProperties"] = reader.GetString(reader.GetOrdinal("DescribeProperties"));
                    dr["PropertieValue"] = reader.GetString(reader.GetOrdinal("PropertieValue"));
                    properties.Rows.Add(dr);
                }
                
            }
            catch(SqlException exception)
            {
                MessageBox.Show(exception.Message,"Ошибка SQL!", MessageBoxButtons.OK,MessageBoxIcon.Error);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка SQL!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
            }
              
            return properties;
        }
    }
    public class DataAccessSpecifications
    {
        public SqlDataReader Test()
        {
            string cs = "server=pavel;uid=pavel;pwd=123456;database=GMSDatabase";
            SqlConnection conn = new SqlConnection(cs);
            SqlCommand selectContact = new SqlCommand("DSelectNumberBillByUser", conn);
            conn.Open();
            SqlDataReader reader = selectContact.ExecuteReader();
            reader.Read();
            return reader;
        }
        public bool DeleteSpecifications(string ConnectionString, string StoredProcedures, int Specification)
        {
            SqlCommand delete = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            delete.Connection = connection;
            connection.ConnectionString = ConnectionString;
            delete.CommandType = CommandType.StoredProcedure;
            delete.CommandText = StoredProcedures;
            delete.Parameters.Add("@IdBill", SqlDbType.Int).Value = Specification;
            try
            {
                connection.Open();
                delete.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }
        public Specification InsertSpecifications(string ConnectionString, string Storedprocedures, int Distributor, int Officer, int Customer, DateTime DateBill)
        {
            SqlCommand insert = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            insert.CommandType = CommandType.StoredProcedure;
            insert.CommandText = Storedprocedures;
            insert.Parameters.Add("@IdDistributor", SqlDbType.Int).Value = Distributor;
            insert.Parameters.Add("@IdOfficer", SqlDbType.Int).Value = Officer;
            insert.Parameters.Add("@IdCustomer", SqlDbType.Int).Value = Customer;
            insert.Parameters.Add("@DateBill", SqlDbType.DateTime).Value = DateBill;
            insert.Connection = connection;
            SqlDataReader reader;
            Specification specification;
            try
            {
                connection.Open();
                reader = insert.ExecuteReader();
                reader.Read();
                specification = CreateSpecifications(reader);
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                connection.Close();
            }
            return specification;
        }

        public List<Specification> SelectSpecificationsBydistributorCustomerOfficer(string ConnectionString, string StoredProcedure, int Distributor, int Customer, List<User> Officers, DateTime DateStart, DateTime dateEnd, int Deleted)
        {
            List<Specification> specifications = new List<Specification>();
            SqlCommand select = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = ConnectionString;
            select.Connection = connection;
            select.CommandType = CommandType.StoredProcedure;
            select.CommandText = StoredProcedure;
            select.Parameters.Add("@IdDistributor", SqlDbType.Int).Value = Distributor;
            select.Parameters.Add("@IdCustomer", SqlDbType.Int).Value = Customer;
            select.Parameters.Add("@DateStart", SqlDbType.DateTime).Value = DateStart;
            select.Parameters.Add("@DateEnd", SqlDbType.DateTime).Value = dateEnd;
            select.Parameters.Add("@Deleted", SqlDbType.Int).Value = Deleted;

            string officers = string.Empty;
            foreach (User user in Officers)
            {
                if (Officers.IndexOf(user) == Officers.Count - 1)
                {
                    officers = officers + user.Login;
                }
                else 
                {
                    officers = user.Login + "', '" + officers;
                }
            }
            officers = "'" + officers + "'";
            select.Parameters.Add("@IdOfficer", SqlDbType.VarChar).Value = officers;
            SqlDataReader reader; 
            try
            {
                connection.Open();
                reader = select.ExecuteReader();
                while (reader.Read())
                {
                    specifications.Add(CreateSpecifications(reader));
                }
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
            }
            return specifications;

        }
        private Specification CreateSpecifications(SqlDataReader reader)
        {
            Specification specification = new Specification(reader.GetInt32(reader.GetOrdinal("IdBill")));
            specification.NumberSpecification = reader.GetInt32(reader.GetOrdinal("NumberBill"));
            specification.DateDischarge = reader.GetDateTime(reader.GetOrdinal("DateBill"));
            return specification;
        }
    }
    #region DataAccesUser
    public class DataAccesUser
    {
        public SqlDataReader Test()
        {
            string cs = "server=pavel;uid=pavel;pwd=123456;database=GMSDatabase";
            SqlConnection conn = new SqlConnection(cs);
            SqlCommand selectContact = new SqlCommand("DSelectUserByLogin 'current'", conn);
            conn.Open();
            SqlDataReader reader = selectContact.ExecuteReader();
            reader.Read();
            return reader;
        }

        public List<User> SelectUsers(string Connectionstring, string StoreProcedures)
        { 
            SqlConnection connection = new SqlConnection();
            SqlCommand selectUsers = new SqlCommand();
            selectUsers.CommandType = CommandType.StoredProcedure;
            selectUsers.CommandText = StoreProcedures;
            selectUsers.Connection = connection;
            connection.ConnectionString = Connectionstring;
            List<User> listUsers = new List<User>();

            try 
            {
                connection.Open();
                SqlDataReader Reader = selectUsers.ExecuteReader();
                while (Reader.Read())
                {
                    listUsers.Add(CreateUser(Reader));
                }
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
            }
            return listUsers;
        }
        public User CreateUser(SqlDataReader reader)
        {
            User user = new User(reader.GetInt32(reader.GetOrdinal("IdUser")));
            user.Name = reader.GetString(reader.GetOrdinal("Name"));
            user.SName = reader.GetString(reader.GetOrdinal("SName"));
            user.TName = reader.GetString(reader.GetOrdinal("TName"));
            user.Login = reader.GetString(reader.GetOrdinal("Login"));
            return user;
        }
    }
    #endregion
    #region DataAccessCustomer
    public class DataAccessCustomer
    {
        public ArrayList Test()
        {
            string cs = "server=pavel;uid=pavel;pwd=123456;database=GMSDatabase";
            SqlConnection conn = new SqlConnection(cs);
            SqlCommand selectContact = new SqlCommand("[dbo].[DSelectCustomers]", conn);

            conn.Open();
            SqlDataReader reader = selectContact.ExecuteReader();
            ArrayList List = new ArrayList();
            while (reader.Read())
            {
                List.Add(CreatePerson(reader));
            }
            conn.Close();

            return List;
        }
        public List<Person> Persons(ArrayList List)
        {
            List<Person> Persons = new List<Person>();
            foreach(object person in List )
            {
                if ((person as Person).ParentId == 0)
                {
                    ChildPersons(List, (person as Person));
                    Persons.Add(person as Person);
                }
            }
            return Persons;
        }
        private void ChildPersons(ArrayList List, Person Parentperson)
        {
            foreach(object person in List )
            {
                if ((person as Person).ParentId == Parentperson.IdContact)
                {
                    ChildPersons(List, (person as Person));
                    Parentperson.PersonArr.Add(person as Person);
                }
            }
        }
        public List<Person> Persons(DataTable personTable)
        {
            List<Person> Persons = new List<Person>();
            DataRow[] DRs = personTable.Select(string.Format("IdParentUser= 0"));
            foreach (DataRow DR in DRs)
            {

                Person P = CreatePerson(DR);
                P.PersonArr.AddRange(ChildPersons(personTable, P));
                Persons.Add(P);
           
            }
            return Persons;
        }
        private Person CreatePerson(SqlDataReader Reader)
        {
            Person person = new Person(Reader.GetInt32(Reader.GetOrdinal("IdUser")));
            person.Name = Reader.GetString(Reader.GetOrdinal("CaptionUser"));
            person.Note = Reader.GetString(Reader.GetOrdinal("Note"));
            person.ParentId = Reader.GetInt32(Reader.GetOrdinal("IdParentUser"));
            return person;
        }
        private Person CreatePerson(DataRow DR)
        {
            Person p = new Person(Convert.ToInt32(DR["IdUser"].ToString()));
            p.Name = DR["CaptionUser"].ToString();
            p.Note = DR["Note"].ToString();
            p.ParentId = Convert.ToInt32(DR["IdParentUser"].ToString());
            return p;
        }
        public Person CreatePerson(int IdPerson, string CaptiojnUser, string Note, int ParentId)
        { 
            Person P = new Person(IdPerson);
            P.Name = CaptiojnUser;
            P.Note = Note;
            P.ParentId = ParentId;
            return P;
        }
        private List<Person> ChildPersons(DataTable personTable, Person parentPerson)
        {
            List<Person> childPersons = new List<Person>();
            DataRow[] DRs = personTable.Select(string.Format("IdParentUser={0}", parentPerson.IdContact));
            foreach (DataRow DR in DRs)
            {

                Person P = CreatePerson(DR);
                P.PersonArr.AddRange(ChildPersons(personTable, P));
                childPersons.Add(P);
           
            }
            return childPersons;
        }
        public void CreatePersonTree(TreeView TV, DataTable DT)
        {
            DataRow[] DRs = DT.Select("IdParentUser=0");
            foreach (DataRow DR in DRs)
            {
                TreeNode ParentNode = new TreeNode();
                ParentNode.Tag = DR["IdUser"].ToString();
                ParentNode.Text = DR["CaptionUser"].ToString();
                ParentNode.ToolTipText = DR["Note"].ToString();
                CreateChildPersons(ParentNode, DT);
                TV.Nodes.Add(ParentNode);
            }
        }
        private void CreateChildPersons(TreeNode ParentNode, DataTable DT)
        {
            DataRow[] DRs = DT.Select(string.Format("IdParentUser={0}", ParentNode.Tag));
            foreach (DataRow DR in DRs)
            {
                TreeNode ChildNode = new TreeNode();
                ChildNode.Tag = DR["IdUser"].ToString();
                ChildNode.Text = DR["CaptionUser"].ToString();
                ChildNode.ToolTipText = DR["Note"].ToString();
                ParentNode.Nodes.Add(ChildNode);
                CreateChildPersons(ChildNode, DT);
            }
        }
        public ArrayList SelectCustomer(string ConnectionString, string storedprocedures)
        {
            SqlConnection connection = new SqlConnection(ConnectionString);
            SqlCommand selectCustomer = new SqlCommand();
            selectCustomer.CommandType = CommandType.StoredProcedure;
            selectCustomer.CommandText = storedprocedures;
            selectCustomer.Connection = connection;
            SqlDataReader reader;
            ArrayList List = new ArrayList();

            try
            {
                connection.Open();
                reader = selectCustomer.ExecuteReader();
                while (reader.Read())
                {
                    List.Add(CreatePerson(reader));
                }
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
            }
            return List;
        }

        public int InsertCustomer(string storedProcedure, Person person, string connectionString)
        {
            SqlCommand insert = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            insert.Connection = connection;
            insert.CommandType = CommandType.StoredProcedure;
            insert.CommandText = storedProcedure;
            insert.Parameters.Add("@IdParentCustomer", SqlDbType.Int).Value = person.ParentId;
            insert.Parameters.Add("@CaptionCustomer", SqlDbType.NVarChar).Value = person.Name;
            insert.Parameters.Add("@Note", SqlDbType.NVarChar).Value = person.Note;

            try
            {
                connection.Open();
                person.IdContact = Convert.ToInt32(insert.ExecuteScalar());
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                connection.Close();
            }
            return person.IdContact;
        }
        public bool UpdateCustomer(string storedProcedure, Person person, string connectionString)
        {
            SqlCommand update = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            update.Connection = connection;
            update.CommandType = CommandType.StoredProcedure;
            update.CommandText = storedProcedure;
            update.Parameters.Add("@IdParentCustomer", SqlDbType.Int).Value = person.ParentId;
            update.Parameters.Add("@CaptionCustomer", SqlDbType.NVarChar).Value = person.Name;
            update.Parameters.Add("@Note", SqlDbType.NVarChar).Value = person.Note;
            update.Parameters.Add("@IdCustomer", SqlDbType.Int).Value = person.IdContact;

            try
            {
                connection.Open();
                update.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }
        public bool DeleteCustomer (string storedProcedure, Person person, string connectionString)
        {
            SqlCommand delete = new SqlCommand();
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            delete.Connection = connection;
            delete.CommandType = CommandType.StoredProcedure;
            delete.CommandText = storedProcedure;
            delete.Parameters.Add("@IdUser", SqlDbType.Int);
            delete.Parameters["@IdUser"].Value = person.IdContact;
            try
            {
                connection.Open();
                delete.ExecuteNonQuery();
            }
            catch (SqlException exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            catch (Exception exception)
            {
                MessageBox.Show(exception.Message, "Ошибка!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }
    }
#endregion
}
